package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.Answer;

public class AnswerDao {

	public AnswerDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	
	

	public ArrayList<Answer> getAllAnswerList() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Answer> answerList = new ArrayList<Answer>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select answer.content, answerId, qid, memid, answer.createdTime from answer,user,quest where questId=qid and answer.uid=userid order by answerId desc");
	
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int answerId = rs.getInt("answerId");
				int qid = rs.getInt("qid");
				String content = rs.getString("content");
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				Answer ans = new Answer();
				
				ans.setAnswerId(answerId); ans.setQid(qid); ans.setContent(content); ans.setMemid(memid); ans.setCreatedTime(createdTime);
				answerList.add(ans);
			}

			return answerList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return answerList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}
	
	// 퀘스트 내 답변 작성한 사람 리스트
	public ArrayList<Answer> getAllAnswerList(int questId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Answer> answerList = new ArrayList<Answer>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select answerId, qid, memid, createdTime from answer,user where answer.uid=userid and qid=? order by answerId desc");
			pstmt.setInt(1, questId);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int answerId = rs.getInt("answerId");
				int qid = rs.getInt("qid");
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				answerList.add(new Answer(answerId, qid, memid, createdTime));
			}

			return answerList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return answerList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}
	
	// 퀘스트 내 답변 작성한 사람 리스트
		public Answer getAnswerByUserId(int questId, int userId) throws Exception {
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			Answer answer = null;
			try {

				con = DriverManager.getConnection(url, user, pass);
				pstmt = con
						.prepareStatement("select answerId, qid, content, uid, memid, createdTime from answer,user where answer.uid=userid and qid=? and uid=?");
				pstmt.setInt(1, questId);
				pstmt.setInt(2, userId);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					int answerId = rs.getInt("answerId");
					int qid = rs.getInt("qid");
					String content = rs.getString("content").trim();
					int uid = rs.getInt("uid");
					String memid = rs.getString("memid");
					Timestamp createdTime = rs.getTimestamp("createdTime");
					answer = new Answer(answerId, qid, content,uid, memid, createdTime);
				}

				return answer;

			} catch (Exception ex) {
				ex.printStackTrace();
				return answer;
			} finally {
				if (rs != null)
					try {
						rs.close();
					} catch (SQLException ex) {
					}
				if (pstmt != null)
					try {
						pstmt.close();
					} catch (SQLException ex) {
					}
				if (con != null)
					try {
						con.close();
					} catch (SQLException ex) {
					}
			}

		}

	// 퀘스트 내 답변 작성한 사람 리스트
	public Answer getAnswerInfo(int answerId) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Answer answer = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select qid, content, uid, memid, answer.createdTime from answer,user where answer.uid=userid and answerId=?");
			pstmt.setInt(1, answerId);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int qid = rs.getInt("qid");
				String content = rs.getString("content").trim();
				int uid = rs.getInt("uid");
				String memid = rs.getString("memid");
				Timestamp createdTime = rs.getTimestamp("createdTime");
				answer = new Answer(answerId, qid, content,uid, memid, createdTime);
			}

			return answer;

		} catch (Exception ex) {
			ex.printStackTrace();
			return answer;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// answer 작성
	public void insertAnswerInfo(Answer newAnswer) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into answer(qid, content, uid, createdTime) values(?,?,?,sysdate())");
			pstmt.setInt(1, newAnswer.getQid());
			pstmt.setString(2, newAnswer.getContent());
			pstmt.setInt(3, newAnswer.getUid());

			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// answer 수정
	public void updateAnswerInfo(Answer updateAnswer) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update answer set content=?, updatedTime=sysdate() where answerId=?");
			pstmt.setString(1, updateAnswer.getContent());
			pstmt.setInt(2, updateAnswer.getAnswerId());
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// quest 만들 때 자동으로 생성
	public void insertAnswerAllMember(int sid, int qid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select uid from participation where isConfirmed=1 and sid=?");
			pstmt.setInt(1, sid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				pstmt = con
						.prepareStatement("insert into answer(qid, content, uid, createdTime) values(?,?,?,sysdate())");
				pstmt.setInt(1, qid);
				pstmt.setString(2, "답변을 작성해주세요.");
				pstmt.setInt(3, rs.getInt("uid"));
				pstmt.executeUpdate();
			}

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}


}